﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;
using ICSharpCode.SharpZipLib.Core;
using ICSharpCode.SharpZipLib.Zip;
using System.IO.Compression;
using GPS.GPS_DAL;
using Microsoft.Office.Interop.Word;

namespace GPS.Controllers.GPS_DanhMuc
{
    public class GPS_HocVienController : FDBController
    {
        #region onit
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public string contentFile { get; set; }
        public void initialCategorySearchAction(ViewModelQLDHV viewModel)
        {
            string DVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            ViewBag.checkuser = DVGTVT_MA;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, viewModel.pHV_DVGTVT_MA);
            viewModel.pHV_DVGTVT_MA = getdvgtvt_Ma;
            string HoDem = "";
            string Ten = "";
            GPS.DAL.Check_User.Substring_hoten(viewModel.pHV_HODEM, ref HoDem, ref Ten);
            viewModel.pHV_HODEM = HoDem;
            viewModel.pHV_TEN = Ten;

            string MaKhoaHoc = null;
            GPS.DAL.Check_User.Substring_Autocomplate(viewModel.pHV_KH_MA, ref MaKhoaHoc);
            viewModel.pHV_KH_MA = MaKhoaHoc;
            ViewBag.HV_HANGDT_MAs = new SelectList(_db.Filter<SHANGDT>(o => o.HANGDT_RANK >= 5), "HANGDT_MA", "HANGDT_TEN");

            ViewBag.HangGPLXs = new SelectList(_db.GetAll<SHANGGPLX>(), "HGPLX_MA", "HGPLX_TEN");
        }

        public void initialCategoryDetailAction(CrudModelITHHOCVIEN _model)
        {
            _model.HV_HS_TTXL_MA = _model.HV_HS_TTXL_MA == null ? null : _db.GetOne<STTXL>(x => x.TTXL_MA == _model.HV_HS_TTXL_MA).TTXL_TEN;
            _model.HV_HS_DVGTVT_MA_NHANHS = _model.HV_HS_DVGTVT_MA_NHANHS == null ? null : _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == _model.HV_HS_DVGTVT_MA_NHANHS).DVGTVT_TEN;
        }

        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        #endregion

        #region Action
        public ActionResult Index(ViewModelQLDHV SearchModel, int? currentPage)
        {
            try
            {
                //checkuser
                initialCategorySearchAction(SearchModel);
                List<CrudModelITHHOCVIEN> model = new List<CrudModelITHHOCVIEN>();

                if (SearchModel.SearchButton == "Xuất danh sách")
                {
                    model = GetListData(SearchModel);
                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\DanhSachHocVien.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx"));
                    // Using the template to create the newFile...
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        int recordIndex = 6;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.HV_HOTEN;
                            myWorksheet.Cells[recordIndex, 3].Value = item.HV_NGAYSINH == null ? "" : item.HV_NGAYSINH.ToString();
                            myWorksheet.Cells[recordIndex, 4].Value = item.HV_SOCMT == null ? "" : item.HV_SOCMT.ToString(); ;
                            myWorksheet.Cells[recordIndex, 5].Value = item.HV_GIOITINH;
                            myWorksheet.Cells[recordIndex, 6].Value = item.HV_NOICT == null ? "" : item.HV_NOICT;
                            recordIndex++;
                        }
                        if (SearchModel.pHV_DVGTVT_MA != null)
                        {
                            myWorksheet.Cells[2, 1].Value = "Cơ sở giao thông vận tải" + _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == SearchModel.pHV_DVGTVT_MA).DVGTVT_TEN; ;
                        }
                        if (SearchModel.pHV_HANGDT_MA != null)
                        {
                            myWorksheet.Cells[3, 1].Value = "Hạng đào tạo: " + _db.GetOne<SHANGDT>(x => x.HANGDT_MA == SearchModel.pHV_DVGTVT_MA).HANGDT_TEN; ;
                        }
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("DanhSachGiaoVien_{0}.xlsx", DateTime.Now.ToShortDateString().Replace("-", "_")));

                }
                else if (SearchModel.SearchButton == "Xuất hợp đồng" || SearchModel.SearchButton == "Xuất thanh lý")
                {
                    model = GetListData(SearchModel);
                    string strZipName = "";
                    if (SearchModel.SearchButton == "Xuất hợp đồng")
                    {
                        strZipName = "DanhSachHopDongDaoTao_{0}.zip";
                        foreach (var item in model)
                        {
                            ExportDHV_hopDong _model = BindDataExport_HopDong(item.HV_MA, item.HV_DVGTVT_MA, item.STT.ToString());
                            if (_model == null)
                            {
                                ViewBag.Message = "Không tìm thấy dữ liệu để xuất danh sách hợp đồng đào tạo.";
                                return RedirectToAction("Index");
                            }
                            byte[] contentFile = null;
                            contentFile = GPS.ENTITYS.EXPORTWORD_DHV_HopDong.EXPORT_HOCVIENHD(Server.MapPath("~/Templates/GPS_Word/HopDongDaoTaoLaiXe1.docx"), _model, "DocxGenericReport");
                            string nameOutput = "HopDongDaoTaoLaiXe_" + DateTime.Now.ToShortDateString() + ".docx";
                            string path = Server.MapPath("~/Templates/FileZip/") + item.STT + "_" + item.HV_KH_MA + "_HopDongDaoTao.docx";
                            System.IO.File.WriteAllBytes(path, contentFile);
                        }
                    }
                    else
                    {
                        strZipName = "DanhSachThanhLyHopDongDaoTao_{0}.zip";
                        foreach (var item in model)
                        {
                            ExportDHV_TLhopDong _model = BindDataExport_ThanhLyHopDong(item.HV_MA, item.HV_DVGTVT_MA, item.STT.ToString());
                            if (_model == null)
                            {
                                ViewBag.Message = "Không tìm thấy dữ liệu để xuất danh sách thanh lý hợp đồng đào tạo.";
                                return RedirectToAction("Index");
                            }
                            byte[] contentFile = null;
                            contentFile = GPS.ENTITYS.EXPORTWORD_DHV_TLHopDong.EXPORT_HOCVIENTLHD(Server.MapPath("~/Templates/GPS_Word/ThanhLyHopDongDaoTaoLaiXe.docx"), _model, "DocxGenericReport");
                            string path = Server.MapPath("~/Templates/FileZip/") + item.STT + "_" + item.HV_KH_MA + "_ThanhLyHopDongDaoTao.docx";
                            System.IO.File.WriteAllBytes(path, contentFile);
                        }
                    }
                    string strMapPath = "~/Templates/FileZip";
                    var context = System.Web.HttpContext.Current;
                    var folderPath = Server.MapPath(strMapPath);
                    var baseOutputStream = new MemoryStream();
                    ZipOutputStream zipOutput = new ZipOutputStream(baseOutputStream);
                    zipOutput.IsStreamOwner = false;
                    zipOutput.SetLevel(3);
                    byte[] buffer = new byte[40096];
                    foreach (var file in Directory.GetFiles(folderPath))
                    {
                        ZipEntry entry = new ZipEntry(Path.GetFileName(file));
                        entry.DateTime = DateTime.Now;
                        zipOutput.PutNextEntry(entry);
                        using (FileStream fs = System.IO.File.OpenRead(file))
                        {
                            int sourceBytes = 0;
                            do
                            {
                                sourceBytes = fs.Read(buffer, 0, buffer.Length);
                                zipOutput.Write(buffer, 0, sourceBytes);
                            } while (sourceBytes > 0);
                        }
                    }
                    zipOutput.Finish();
                    zipOutput.Close();
                    string[] files = System.IO.Directory.GetFiles(folderPath);
                    // Copy the files and overwrite destination files if they already exist.
                    foreach (string s in files)
                    {
                        System.IO.File.Delete(s);
                    }
                    /* Set position to 0 so that cient start reading of the stream from the begining */
                    baseOutputStream.Position = 0;
                    /* Set custom headers to force browser to download the file instad of trying to open it */
                    return File(baseOutputStream, "application/x-zip-compressed", string.Format(strZipName, SearchModel.pHV_KH_MA));
                }
                else if (SearchModel.SearchButton == "Xuất báo cáo 1")
                {
                    ExportDHV_BC1 _model = BindDataExport_BaoCao1(SearchModel);
                    if (_model == null)
                    {
                        ViewBag.Message = "Không tìm thấy dữ liệu để xuất danh sách báo cáo 1.";
                        return RedirectToAction("Index");
                    }
                    //byte[] contentFile = null;
                    //contentFile = GPS.ENTITYS.EXPORTWORD_DHV_BC1.EXPORT_HOCVIENBC1(Server.MapPath("~/Templates/GPS_Word/BC1.docx"), _model, "DocxGenericReport");
                    //string nameOutput = string.Format("DanhSachBaoCao1_{0}_{0}.docx", _model.KH_TEN, DateTime.Now.ToShortDateString().Replace("/", "_"));
                    //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
                    //khoi tao Word app:
                    Microsoft.Office.Interop.Word.Application _app = null;
                    Microsoft.Office.Interop.Word.Document _doc = null;
                    object _pathFile = null;
                    object ob = System.Reflection.Missing.Value;

                    try
                    {
                        // ExportDHV_BC1 _model = BindDataExport_BaoCao1(SearchModel);
                        DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_DSHVLX"
                                                        , new OracleParameter("PDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pHV_DVGTVT_MA) ? DBNull.Value : (object)SearchModel.pHV_DVGTVT_MA, ParameterDirection.Input)
                                                        , new OracleParameter("PKH_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pHV_KH_MA) ? DBNull.Value : (object)SearchModel.pHV_KH_MA, ParameterDirection.Input)
                                                        , new OracleParameter("PRETHEADER", OracleDbType.RefCursor, ParameterDirection.Output)
                                                        , new OracleParameter("PRETDSHVLX", OracleDbType.RefCursor, ParameterDirection.Output));
                        //GiangTD3 - 2017-08-29: 
                        string filename = "BC1_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".docx";
                        String GENERATED_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_WordExport/" + filename);
                        String TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/Template_BC1.dotx");
                        if (ds == null || ds.Tables.Count <= 0)
                        {
                            ViewBag.Message = "Không tìm thấy dữ liệu để xuất danh sách báo cáo 1.";
                            return RedirectToAction("Index");
                        }
                        //Template_BC1
                        //byte[] contentFile = null;
                        //contentFile = GPS.ENTITYS.EXPORTWORD_DHV_BC1.EXPORT_HOCVIENBC1(Server.MapPath("~/Templates/GPS_Word/BC1.docx"), _model, "DocxGenericReport");
                        //string nameOutput = string.Format("DanhSachBaoCao1_{0}_{0}.docx", _model.KH_TEN, DateTime.Now.ToShortDateString().Replace("/", "_"));
                        //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);

                        _pathFile = TEMPLATE_FILE_NAME;
                        _app = new Microsoft.Office.Interop.Word.Application();
                        _app.Visible = false;

                        _doc = _app.Documents.Add(ref _pathFile, ref ob, ref ob, ref ob);

                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            System.Data.DataTable vDataTable = ds.Tables[0];

                            Dictionary<string, string> dicKHOAHOC = new Dictionary<string, string>();
                            dicKHOAHOC.Add("DVGTVT_TEN", vDataTable.Rows[0]["DVGTVT_TEN"].ToString().ToUpper());

                            dicKHOAHOC.Add("KH_TEN", vDataTable.Rows[0]["KH_TEN"].ToString());
                            dicKHOAHOC.Add("KH_HANGGPLX_MA", vDataTable.Rows[0]["KH_HANGGPLX_MA"].ToString());
                            dicKHOAHOC.Add("KH_THOIGIANDT", vDataTable.Rows[0]["KH_THOIGIANDT"].ToString());
                            // dicKHOAHOC.Add("KH_TONGHV", vDataTable.Rows[0]["KH_TONGHV"].ToString());
                            //KH_NGAYKG1
                            var _NGAY_KG = Convert.ToDateTime(vDataTable.Rows[0]["KH_NGAYKG1"]);

                            dicKHOAHOC.Add("dd", (_NGAY_KG.Day < 10 ? "0" + _NGAY_KG.Day.ToString() : _NGAY_KG.Day.ToString()));
                            dicKHOAHOC.Add("mm", (_NGAY_KG.Month < 10 ? "0" + _NGAY_KG.Month.ToString() : _NGAY_KG.Month.ToString()));
                            dicKHOAHOC.Add("yyyy", _NGAY_KG.Year.ToString());

                            var _NGAY_BG = Convert.ToDateTime(vDataTable.Rows[0]["KH_NGAYBG1"]);
                            //   var _now = DateTime.Now;
                            dicKHOAHOC.Add("dd2", (_NGAY_BG.Day < 10 ? "0" + _NGAY_BG.Day.ToString() : _NGAY_BG.Day.ToString()));
                            dicKHOAHOC.Add("mm2", (_NGAY_BG.Month < 10 ? "0" + _NGAY_BG.Month.ToString() : _NGAY_BG.Month.ToString()));
                            dicKHOAHOC.Add("yyyy2", _NGAY_BG.Year.ToString());


                            foreach (Microsoft.Office.Interop.Word.Field field in _doc.Fields)
                            {
                                string fieldName = field.Code.Text.Substring(11, field.Code.Text.IndexOf("\\") - 12).Trim();
                                if (dicKHOAHOC.ContainsKey(fieldName))
                                {
                                    field.Select();
                                    _app.Selection.TypeText(dicKHOAHOC[fieldName]);
                                }
                            }


                        }
                        //Thông tin  học viên
                        if (ds.Tables[1].Rows.Count > 0)
                        {

                            System.Data.DataTable vDataTable = ds.Tables[1];
                            Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[2];


                            int lenrow = vDataTable.Rows.Count;
                            int lencol = vDataTable.Columns.Count;

                            for (int i = 0; i < lenrow; ++i)
                            {

                                //tbl.Rows.Add(ref ob);

                                for (int j = 0; j < lencol; ++j)
                                {


                                    tbl.Cell(i + 4, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                                    //tbl.Cell(i + 2, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                    tbl.Cell(i + 4, j + 1).Range.Font.Size = 10F;

                                }
                                if (i < lenrow - 1)
                                 tbl.Rows.Add(ref ob);
                            }
                        }
                       //  _doc.Tables[2].Rows[ds.Tables[1].Rows.Count + 2].Delete();

                        try
                        {

                            object filenamesave = GENERATED_FILE_NAME;
                            _doc.SaveAs2(ref filenamesave);
                            _doc.Close(ref ob, ref ob, ref ob);
                            _doc = null;
                            _app.Quit(ref ob, ref ob, ref ob);
                            _app = null;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            if (_doc != null)
                            {
                                _doc.Close(ref ob, ref ob, ref ob);
                                _doc = null;
                            }
                            if (_app != null)
                            {
                                _app.Quit(ref ob, ref ob, ref ob);
                                _app = null;
                            }

                        }

                        return File(GENERATED_FILE_NAME, "application/docx", filename);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (_doc != null)
                        {
                            _doc.Close(ref ob, ref ob, ref ob);
                            _doc = null;
                        }
                        if (_app != null)
                        {
                            _app.Quit(ref ob, ref ob, ref ob);
                            _app = null;
                        }
                    }
                }
                else if (SearchModel.SearchButton == "Tìm kiếm")
                {
                    if (!string.IsNullOrEmpty(SearchModel.pHV_DVGTVT_MA)
                    && (!string.IsNullOrEmpty(SearchModel.pHV_HANGDT_MA)
                    || !string.IsNullOrEmpty(SearchModel.pHV_HODEM)
                    || !string.IsNullOrEmpty(SearchModel.pHV_HS_HANGGPLXDACO)
                    || !string.IsNullOrEmpty(SearchModel.pHV_KH_MA)
                    || !string.IsNullOrEmpty(SearchModel.pHV_MA)
                    || string.IsNullOrEmpty(SearchModel.pHV_SOCMT)
                    || !string.IsNullOrEmpty(SearchModel.pROW)))
                    {
                        model = GetListData(SearchModel);
                    }
                    ViewBag.TotalRow = model == null ? "0" : model.Count().ToString();
                    var pageIndex = SearchModel.Page ?? 1;
                    SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                    return View(SearchModel);
                }
                else
                {
                    ViewBag.TotalRow = model == null ? "0" : model.Count().ToString();
                    var pageIndex = SearchModel.Page ?? 1;
                    SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                    return View(SearchModel);
                }
            }
            catch (Exception ex)
            {
                ViewBag.Message = string.Format("Lỗi hệ thống vui lòng liên hệ với admin.Lỗi: {0}", ex.ToString());
                return View(SearchModel);
            }
        }
        public ActionResult Detail(string id, string id1)
        {
            try
            {
                CrudModelITHHOCVIEN _model = new CrudModelITHHOCVIEN();
                _model = GetItemData(id, id1);
                initialCategoryDetailAction(_model);
                _model.HV_GIOITINH = _model.HV_GIOITINH == "M" ? "Nam" : "Nữ";
                return PartialView(_model);
            }
            catch (Exception ex)
            {
                ViewBag.Message = string.Format("Lỗi hệ thống vui lòng liên hệ với admin.Lỗi: {0}", ex.ToString());
                return RedirectToAction("Index");
            }
        }
        #endregion

        #region Export Word
        /// <summary>
        /// Export word chi tiết học viên
        /// </summary>
        /// <param name="id"></param>
        /// <param name="id1"></param>
        /// <returns></returns>
        public ActionResult ExportWord(string id, string id1)
        {
            try
            {
                CrudModelITHHOCVIEN _model = new CrudModelITHHOCVIEN();
                _model = GetItemData(id, id1);
                _model.HV_GIOITINH = _model.HV_GIOITINH == "F" ? "Nữ" : "Nam";
                //_model.HV_NGAYSINH = _model.HV_NGAYSINh
                initialCategoryDetailAction(_model);
                byte[] contentFile = null;
                contentFile = GPS.ENTITYS.EXPORTWORD_QLHV.EXPORT_QLHV(Server.MapPath("~/Templates/GPS_Word/HocVien_ThongTin.docx"), _model, "DocxGenericReport");
                string nameOutput = string.Format("LyLichHocVien_{0}.docx", _model.HV_HOTEN.Replace(" ", "_"));
                return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("ErrorMsg", ex.Message);
                return RedirectToAction("Index");
            }
        }
        public ActionResult ExportWordHopDong(string id, string id1, string id2)
        {
            try
            {
                string _HV_MA = id;
                string _HV_DVGTVT_MA = id1;
                string _STT = id2.ToString();
                ExportDHV_hopDong _model = BindDataExport_HopDong(_HV_MA, _HV_DVGTVT_MA, _STT);
                byte[] contentFile = null;
                contentFile = GPS.ENTITYS.EXPORTWORD_DHV_HopDong.EXPORT_HOCVIENHD(Server.MapPath("~/Templates/GPS_Word/HopDongDaoTaoLaiXe1.docx"), _model, "DocxGenericReport");
                string nameOutput = string.Format("HopDongDaoTaoLaiXe_{0}_{1}.docx", _model.HOTEN.Replace(" ", "_"), _model.HV_NGAYSINH.Replace("/", "_"));
                return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
            }
            catch (Exception ex)
            {
                ViewBag.Message = string.Format("Lỗi hệ thống vui lòng liên hệ với admin.Lỗi: {0}", ex.ToString());
                return RedirectToAction("Index");
            }
        }
        public ActionResult ExportWordThanhLyHopDong(string id, string id1, string id2)
        {
            try
            {
                string _HV_MA = id;
                string _HV_DVGTVT_MA = id1;
                string _STT = id2.ToString();
                ExportDHV_TLhopDong _model = BindDataExport_ThanhLyHopDong(_HV_MA, _HV_DVGTVT_MA, _STT);
                byte[] contentFile = null;
                contentFile = GPS.ENTITYS.EXPORTWORD_DHV_TLHopDong.EXPORT_HOCVIENTLHD(Server.MapPath("~/Templates/GPS_Word/ThanhLyHopDongDaoTaoLaiXe.docx"), _model, "DocxGenericReport");
                string nameOutput = string.Format("ThanhLyHopDongDaoTaoLaiXe_{0}_{1}.docx", _model.HOTEN.Replace(" ", "_"), _model.HV_NGAYSINH.Replace("/", "_"));
                return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
            }
            catch (Exception ex)
            {
                ViewBag.Message = string.Format("Lỗi hệ thống vui lòng liên hệ với admin.Lỗi: {0}", ex.ToString());
                return RedirectToAction("Index");
            }
        }
        #endregion

        #region Getdatabinding
        public List<CrudModelITHHOCVIEN> GetListData(ViewModelQLDHV SearchModel)
        {
            //try
            //{
            var entities = Singleton<GPS_DAL.GPS_HocVienDAO>.Inst.GPS_GETDS_SEARCH_HOCVIEN1(SearchModel.pHV_DVGTVT_MA
                                                                                           , SearchModel.pHV_MA
                                                                                           , SearchModel.pHV_KH_MA
                                                                                           , SearchModel.pHV_HOTEN
                                                                                           , SearchModel.pHV_SOCMT
                                                                                           , SearchModel.pHV_HANGDT_MA
                                                                                           , SearchModel.pHV_HS_HANGGPLXDACO
                                                                                           , SearchModel.pROW
                                                                                          );
            return entities;
        }
        public CrudModelITHHOCVIEN GetItemData(string pHV_MA, string pHV_DVGTVT_MA)
        {
            var entities = Singleton<GPS_DAL.GPS_HocVienDAO>.Inst.GPS_GETDS_SEARCH_HOCVIEN1(pHV_DVGTVT_MA
                                                                                           , pHV_MA
                                                                                           , ""
                                                                                           , ""
                                                                                           , ""
                                                                                           , ""
                                                                                           , ""
                                                                                           , "1"
                                                                                          ).FirstOrDefault();
            return entities;
        }
        public ExportDHV_hopDong BindDataExport_HopDong(string _HV_MA, string _HV_DVGTVT_MA, string STT)
        {
            ExportDHV_hopDong _model = new ExportDHV_hopDong();
            DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_HOPDONG"
                                           , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_HV_DVGTVT_MA) ? DBNull.Value : (object)_HV_DVGTVT_MA, ParameterDirection.Input)
                                           , new OracleParameter("pHV_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_HV_MA) ? DBNull.Value : (object)_HV_MA, ParameterDirection.Input)
                                           , new OracleParameter("pRetBenA", OracleDbType.RefCursor, ParameterDirection.Output)
                                           , new OracleParameter("pRetBenB", OracleDbType.RefCursor, ParameterDirection.Output)
                                           , new OracleParameter("pRetCTDT", OracleDbType.RefCursor, ParameterDirection.Output)
                                          );
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {

                _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["DVGTVT_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEN"].ToString();
                _model.DVGTVT_SOGP = ds.Tables[0].Rows[i]["DVGTVT_SOGP"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_SOGP"].ToString();
                if (ds.Tables[0].Rows[i]["DVGTVT_NGAYGP"] != null)
                {
                    _model.DVGTVT_NGAYGP = Convert.ToDateTime(ds.Tables[0].Rows[i]["DVGTVT_NGAYGP"]).ToString("dd/mm/yyyy");
                }
                _model.DVGTVT_LANHDAO = ds.Tables[0].Rows[i]["DVGTVT_LANHDAO"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_LANHDAO"].ToString();
                _model.DVGTVT_ADD = ds.Tables[0].Rows[i]["DVGTVT_ADD"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_ADD"].ToString();
                _model.DVGTVT_TEL = ds.Tables[0].Rows[i]["DVGTVT_TEL"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEL"].ToString();
                _model.DVGTVT_FAX = ds.Tables[0].Rows[i]["DVGTVT_FAX"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_FAX"].ToString();
            }
            for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
            {
                _model.HOTEN = ds.Tables[1].Rows[i]["HOTEN"] == null ? "" : ds.Tables[1].Rows[i]["HOTEN"].ToString();
                _model.HV_NGAYSINH = ds.Tables[1].Rows[i]["HV_NGAYSINH"] == null ? "" : ds.Tables[1].Rows[i]["HV_NGAYSINH"].ToString();
                _model.HV_NOICT = ds.Tables[1].Rows[i]["HV_NOICT"] == null ? "" : ds.Tables[1].Rows[i]["HV_NOICT"].ToString();
                _model.HV_DANGKY = ds.Tables[1].Rows[i]["HV_DANGKY"] == null ? "" : ds.Tables[1].Rows[i]["HV_DANGKY"].ToString();
                _model.KH_TEN = ds.Tables[1].Rows[i]["KH_TEN"] == null ? "" : ds.Tables[1].Rows[i]["KH_TEN"].ToString();
            }
            _model.LISTHopDong = COMMON.Helpers.MapDataTableToList<ExportDHV_hopDong>(ds.Tables[2]);
            _model.DATETIME_NOW = "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm" + DateTime.Now.Year.ToString();
            _model.SOHOPDONG = STT + "/" + _model.KH_TEN;
            return _model;
        }
        public ExportDHV_TLhopDong BindDataExport_ThanhLyHopDong(string _HV_MA, string _HV_DVGTVT_MA, string STT)
        {
            ExportDHV_TLhopDong _model = new ExportDHV_TLhopDong();
            DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_TLHOPDONG"
                                                              , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_HV_DVGTVT_MA) ? DBNull.Value : (object)_HV_DVGTVT_MA, ParameterDirection.Input)
                                                              , new OracleParameter("pHV_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_HV_MA) ? DBNull.Value : (object)_HV_MA, ParameterDirection.Input)
                                                              , new OracleParameter("pRetBenA", OracleDbType.RefCursor, ParameterDirection.Output)
                                                               , new OracleParameter("pRetBenB", OracleDbType.RefCursor, ParameterDirection.Output)
                                                             );
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["DVGTVT_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEN"].ToString();
                _model.DVGTVT_SOGP = ds.Tables[0].Rows[i]["DVGTVT_SOGP"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_SOGP"].ToString();
                if (ds.Tables[0].Rows[i]["DVGTVT_NGAYGP"] != null)
                {
                    _model.DVGTVT_NGAYGP = Convert.ToDateTime(ds.Tables[0].Rows[i]["DVGTVT_NGAYGP"].ToString());
                }
                _model.DVGTVT_LANHDAO = ds.Tables[0].Rows[i]["DVGTVT_LANHDAO"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_LANHDAO"].ToString();
                _model.DVGTVT_ADD = ds.Tables[0].Rows[i]["DVGTVT_ADD"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_ADD"].ToString();
                _model.DVGTVT_TEL = ds.Tables[0].Rows[i]["DVGTVT_TEL"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEL"].ToString();
                _model.DVGTVT_FAX = ds.Tables[0].Rows[i]["DVGTVT_FAX"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_FAX"].ToString();
            }

            for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
            {
                _model.HOTEN = ds.Tables[1].Rows[i]["HOTEN"] == null ? "" : ds.Tables[1].Rows[i]["HOTEN"].ToString();
                _model.HV_NGAYSINH = ds.Tables[1].Rows[i]["HV_NGAYSINH"] == null ? "" : ds.Tables[1].Rows[i]["HV_NGAYSINH"].ToString();
                _model.HV_NOICT = ds.Tables[1].Rows[i]["HV_NOICT"] == null ? "" : ds.Tables[1].Rows[i]["HV_NOICT"].ToString();
                _model.HV_DANGKY = ds.Tables[1].Rows[i]["HV_DANGKY"] == null ? "" : ds.Tables[1].Rows[i]["HV_DANGKY"].ToString();
                _model.KH_TEN = ds.Tables[1].Rows[i]["KH_TEN"] == null ? "" : ds.Tables[1].Rows[i]["KH_TEN"].ToString();
            }
            _model.DATETIME_NOW = "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm" + DateTime.Now.Year.ToString();
            _model.SOHOPDONG = STT + "/" + _model.KH_TEN;
            return _model;
        }
        public ExportDHV_BC1 BindDataExport_BaoCao1(ViewModelQLDHV model)
        {
            ExportDHV_BC1 _model = new ExportDHV_BC1();
            DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_DSHVLX"
                                                    , new OracleParameter("PDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(model.pHV_DVGTVT_MA) ? DBNull.Value : (object)model.pHV_DVGTVT_MA, ParameterDirection.Input)
                                                    , new OracleParameter("PKH_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(model.pHV_KH_MA) ? DBNull.Value : (object)model.pHV_KH_MA, ParameterDirection.Input)
                                                    , new OracleParameter("PRETHEADER", OracleDbType.RefCursor, ParameterDirection.Output)
                                                    , new OracleParameter("PRETDSHVLX", OracleDbType.RefCursor, ParameterDirection.Output));
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["DVGTVT_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEN"].ToString();
                _model.KH_HANGGPLX_MA = ds.Tables[0].Rows[i]["KH_HANGGPLX_MA"] == null ? "" : ds.Tables[0].Rows[i]["KH_HANGGPLX_MA"].ToString();
                _model.KH_TEN = ds.Tables[0].Rows[i]["KH_TEN"] == null ? "" : ds.Tables[0].Rows[i]["KH_TEN"].ToString();
                _model.KH_THOIGIANDT = ds.Tables[0].Rows[i]["KH_NGAYBG"] == null ? "" : ds.Tables[0].Rows[i]["KH_NGAYBG"].ToString();
                _model.KH_NGAYKG = ds.Tables[0].Rows[i]["KH_NGAYKG"] == null ? "" : ds.Tables[0].Rows[i]["KH_NGAYKG"].ToString();
                _model.KH_THOIGIANDT = ds.Tables[0].Rows[i]["KH_THOIGIANDT"] == null ? "" : ds.Tables[0].Rows[i]["KH_THOIGIANDT"].ToString();
            }
            _model.LIST_HS = COMMON.Helpers.MapDataTableToList<ExportDHV_BC1>(ds.Tables[1]);
            int icount = 1;
            foreach (var item in _model.LIST_HS)
            {
                item.STT = icount.ToString();
                icount++;
            }
            _model.DATETIME_NOW = "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm" + DateTime.Now.Year.ToString();
            return _model;
        }
        #endregion

        #region"Ajax"

        [HttpPost]
        public ActionResult getKH_MAs(string ma_DVGT)
        {
            string _ma = ma_DVGT.Split(':')[0];
            TempData["DVGTVT_MA"] = ma_DVGT;
            var districts = _db.Filter<DKH>(d => d.KH_DVGTVT_MA == _ma).Select(a => "<option value='" + a.KH_MA + "'>" + a.KH_MA + "</option>");
            return Content(string.Join("", districts));
        }

        [HttpPost]
        public ActionResult getHV_MA(string gv_DVGTVT_MA)
        {
            string strHV_MA = gv_DVGTVT_MA + "-" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss").Replace(" ", "-").Replace(":", "");

            return Content(string.Join("", strHV_MA));
        }

        public JsonResult AutoCompleteNOICT_DVHC_MA(string term)
        {
            var result = _db.Filter<SDONVIHC>(o => o.DVHC_MA.Contains(term) || o.DVHC_TENDAYDU.ToUpper().Contains(term.ToUpper()));
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public JsonResult AutoCompleteDVGTVT_MA(string term)
        {
            TempData["DVGTVT_MA"] = term;
            var result = _db.Filter<SDONVIGTVT>(o => (o.DVGTVT_MAQL == "40")
                                                    && (o.DVGTVT_MA.Contains(term) || o.DVGTVT_TEN.ToUpper().Contains(term.ToUpper()))
                                                    && (o.DVGTVT_ACTIVE == "1"));
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public JsonResult AutoCompleteKH_MA(string term, string dvgtvt_ma)
        {
            string strDVGTVT_MA = dvgtvt_ma.Split(':')[0];
            var result = _db.Filter<DKH>(o => (o.KH_TEN.ToUpper().StartsWith(term.ToUpper()) || o.KH_MA.ToUpper().StartsWith(term.ToUpper()))
                                               && (o.KH_DVGTVT_MA == strDVGTVT_MA)
                                               && (o.KH_HANGDT_RANK >= 5)
                                               && (o.KH_ACTIVE == "1"));
            // var result = Singleton<GPS_HocVienDAO>.Inst.GPS_GETDS_KHOAHOC_HOCVIEN1(strDVGTVT_MA); 
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region disable
        //#region Create
        //public ActionResult Create()
        //{
        //    initialCategoryCreateAction();

        //    return View(new CrudModelDHV());
        //}

        //[HttpPost]
        //public ActionResult Create(CrudModelDHV model)
        //{
        //    if (ModelState.IsValid)
        //    {
        //        DHV _model = new DHV();
        //        GPS.COMMON.Helpers.CopyObject<DHV>(model, ref _model, new List<string> { "HV_ACTIVE" });
        //        _model.HV_ACTIVE = (model.HV_ACTIVE == false ? "0" : "1");
        //        _model.HV_DVGTVT_MA = model.HV_DVGTVT_MA.Split(':')[0];
        //        _model.ID = EFSequence.GetNextValue("SEQ_DHV_ID");
        //        if (!string.IsNullOrWhiteSpace(model.HV_NOITT_DVHC_MA))
        //        {
        //            _model.HV_NOITT_DVHC_MA = model.HV_NOITT_DVHC_MA.Split(':')[0];
        //        }

        //        if (!string.IsNullOrWhiteSpace(model.HV_NOITT_DVHC_DVQL))
        //        {
        //            _model.HV_NOITT_DVHC_DVQL = model.HV_NOITT_DVHC_DVQL.Split(':')[0];
        //        }
        //        _model.HV_MA = GetIdentity_HVMa(_model.HV_DVGTVT_MA);
        //        _model.HV_NGUOINHAP = User.Identity.Name;
        //        _model.HV_NGAYNHAP = DateTime.Now;
        //        _db.Insert<DHV>(_model);
        //        return RedirectToAction("Index");
        //    }
        //    return View(model);
        //}
        //public void initialCategoryCreateAction()
        //{
        //    ViewBag.HV_DVGTVT_MAs = new SelectList(_db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");
        //    ViewBag.HV_HANGDT_MAs = new SelectList(_db.GetAll<SHANGDT>(), "HANGDT_MA", "HANGDT_TEN");
        //    ViewBag.HV_NOICAPCMTs = new SelectList(_db.Filter<SDONVIHC>(o => o.DVHC_LOAI == "TI"), "DVHC_MA", "DVHC_TENDAYDU");
        //    ViewBag.HV_QUOCTICH_MAs = new SelectList(_db.GetAll<SQUOCTICH>(), "QTICH_MA", "QTICH_TENEN");
        //    ViewBag.DEFAULT_VALUE_DDL = CategoryCommon.DEFAULT_VALUE_DDL;
        //}
        //#endregion

        //#region Edit
        //public ActionResult Edit(decimal id)
        //{

        //    DHV model = _db.GetOne<DHV>(o => o.ID == id);
        //    CrudModelDHV _model = new CrudModelDHV();
        //    GPS.COMMON.Helpers.CopyObject<CrudModelDHV>(model, ref _model, new List<string> { "HV_ACTIVE" });
        //    _model.HV_ACTIVE = (model.HV_ACTIVE == "0" ? false : true);

        //    var dvgtvt = _db.GetOne<SDONVIGTVT>(o => o.DVGTVT_MA == model.HV_DVGTVT_MA);
        //    _model.HV_DVGTVT_MA = dvgtvt.DVGTVT_MA + ":" + dvgtvt.DVGTVT_TEN;

        //    if (!string.IsNullOrWhiteSpace(model.HV_NOITT_DVHC_MA))
        //    {
        //        var dvghc = _db.GetOne<SDONVIHC>(o => o.DVHC_MA == model.HV_NOITT_DVHC_MA);
        //        _model.HV_NOITT_DVHC_MA = dvghc.DVHC_MA + ":" + dvghc.DVHC_TENDAYDU;
        //    }

        //    if (!string.IsNullOrWhiteSpace(model.HV_NOITT_DVHC_DVQL))
        //    {
        //        var dvghc = _db.GetOne<SDONVIHC>(o => o.DVHC_MA == model.HV_NOITT_DVHC_DVQL);
        //        _model.HV_NOITT_DVHC_DVQL = dvghc.DVHC_MA + ":" + dvghc.DVHC_TENDAYDU;
        //    }

        //    initialCategoryEditAction(_model);
        //    return View(_model);
        //}

        //[HttpPost]
        //public ActionResult Edit(CrudModelDHV model)
        //{
        //    if (ModelState.IsValid)
        //    {
        //        var _model = _db.GetOne<DHV>(o => o.ID == model.ID);
        //        GPS.COMMON.Helpers.CopyObject<DHV>(model, ref _model, new List<string> { "HV_ACTIVE", "HV_NGUOINHAP", "HV_NGAYNHAP" });
        //        _model.HV_DVGTVT_MA = model.HV_DVGTVT_MA.Split(':')[0];

        //        if (!string.IsNullOrWhiteSpace(model.HV_NOITT_DVHC_MA))
        //        {
        //            _model.HV_NOITT_DVHC_MA = model.HV_NOITT_DVHC_MA.Split(':')[0];
        //        }

        //        if (!string.IsNullOrWhiteSpace(model.HV_NOITT_DVHC_DVQL))
        //        {
        //            _model.HV_NOITT_DVHC_DVQL = model.HV_NOITT_DVHC_DVQL.Split(':')[0];
        //        }
        //        _model.HV_NGUOISUA = User.Identity.Name;
        //        _model.HV_NGAYSUA = DateTime.Now;


        //        _db.Update<DHV>(_model);
        //        return RedirectToAction("Index");
        //    }

        //    return View(model);
        //}


        //public string GetIdentity_HVMa(string gv_DVGTVT_MA)
        //{
        //    string rt = "";
        //    string strHV_MA = gv_DVGTVT_MA + "-" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss").Replace(" ", "-").Replace(":", "");
        //    rt = string.Join("", strHV_MA);
        //    return rt;
        //}
        //public void initialCategoryEditAction(CrudModelDHV view)
        //{
        //    ViewBag.HV_DVGTVT_MAs = new SelectList(_db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");
        //    ViewBag.HV_HANGDT_MAs = new SelectList(_db.GetAll<SHANGDT>(), "HANGDT_MA", "HANGDT_TEN");
        //    ViewBag.HV_NOICAPCMTs = new SelectList(_db.Filter<SDONVIHC>(o => o.DVHC_LOAI == "TI"), "DVHC_MA", "DVHC_TENDAYDU");
        //    ViewBag.HV_QUOCTICH_MAs = new SelectList(_db.GetAll<SQUOCTICH>(), "QTICH_MA", "QTICH_TENEN");


        //    ViewBag.DEFAULT_VALUE_DDL = CategoryCommon.DEFAULT_VALUE_DDL;
        //    string _ma = view.HV_DVGTVT_MA.Split(':')[0];
        //    var khoahocs = _db.Filter<DKH>(o => o.KH_DVGTVT_MA == _ma);


        //    ViewBag.KH_MAs = new SelectList(khoahocs, "KH_MA", "KH_TEN");


        //}
        //#endregion

        //#region Delete
        //public ActionResult Delete(decimal id)
        //{
        //    CrudModelDHV _model = new CrudModelDHV();
        //    DHV model = _db.GetOne<DHV>(o => o.ID == id);
        //    _db.DeleteItem<DHV>(model);
        //    return RedirectToAction("Index");
        //}
        //#endregion

        #endregion
    }
}